package netbay;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import util.jQgrid.JQGridRow;

public class TransactionTraffic {
	public static final String PAY_BANK = "PAYBANK";
	public static final String PAY_TCD = "PAYTCD";
	public static final String TCD_Name = "TCD";
	public static final String JPMC_NAME = "JPMC";
	private Integer id;
	private Integer transactionId;
	private String eventTime;
	private String process;
	private String action;
	private String tcdAttach;
	private String tcdCtrl;
	private String jpmcAttach;
	private String jpmcCtrl;

	private String tcdMsgId;
	private String tcdRefMsgId;
	private String jpmcMsgId;
	private String jpmcRefMsgId;
	private String tcdPartyId;
	private String tcdPartyType;
	private String jpmcPartyId;
	private String jpmcPartyType;
	private String convertFile;
	private String actionType;
	private String type;
	
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public Integer getTransactionId() {
		return transactionId;
	}
	public void setTransactionId(Integer transactionId) {
		this.transactionId = transactionId;
	}
	public String getActionType() {
		return actionType;
	}
	public void setActionType(String actionType) {
		this.actionType = actionType;
	}
	
	public String getType() {
		return type;
	}
	public void setType(String type) {
		this.type = type;
	}
	public String getAction() {
		return action;
	}
	public void setAction(String action) {
		this.action = action;
	}
	public String getProcess() {
		return process;
	}
	public void setProcess(String process) {
		this.process = process;
	}
	public String getEventTime() {
		return eventTime;
	}
	public void setEventTime(String time) {
		this.eventTime = time;
	}
	
	public String getTcdMsgId() {
		return tcdMsgId;
	}

	public void setTcdMsgId(String tcdMsgId) {
		this.tcdMsgId = tcdMsgId;
	}

	public String getTcdRefMsgId() {
		return tcdRefMsgId;
	}

	public void setTcdRefMsgId(String tcdRefMsgId) {
		this.tcdRefMsgId = tcdRefMsgId;
	}

	public String getJpmcMsgId() {
		return jpmcMsgId;
	}

	public void setJpmcMsgId(String jpmcMsgId) {
		this.jpmcMsgId = jpmcMsgId;
	}
	
	public String getTcdPartyId() {
		return tcdPartyId;
	}

	public void setTcdPartyId(String tcdPartyId) {
		this.tcdPartyId = tcdPartyId;
	}

	public String getTcdPartyType() {
		return tcdPartyType;
	}

	public void setTcdPartyType(String tcdPartyType) {
		this.tcdPartyType = tcdPartyType;
	}

	public String getJpmcPartyId() {
		return jpmcPartyId;
	}

	public void setJpmcPartyId(String jpmcPartyId) {
		this.jpmcPartyId = jpmcPartyId;
	}

	public String getJpmcPartyType() {
		return jpmcPartyType;
	}

	public void setJpmcPartyType(String jpmcPartyType) {
		this.jpmcPartyType = jpmcPartyType;
	}
	
	public String getTcdAttach() {
		return tcdAttach;
	}
	
	public void setTcdAttach(String tcdAttach) {
		this.tcdAttach = tcdAttach;
	}

	public String getTcdCtrl() {
		return tcdCtrl;
	}

	public void setTcdCtrl(String tcdCtrl) {
		this.tcdCtrl = tcdCtrl;
	}

	public String getJpmcAttach() {
		return jpmcAttach;
	}

	public void setJpmcAttach(String jpmcAttach) {
		this.jpmcAttach = jpmcAttach;
	}

	public String getJpmcCtrl() {
		return jpmcCtrl;
	}

	public void setJpmcCtrl(String jpmcCtrl) {
		this.jpmcCtrl = jpmcCtrl;
	}
	
	public String getConvertFile() {
		return convertFile;
	}
	
	public void setConvertFile (String convertFile){
		this.convertFile = convertFile;
	}
	

	public static TransactionTraffic convertTcdCtrlFile(File ctrlFile) {
		Properties prop = new Properties();
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(ctrlFile);
			if (fis != null) {
				prop.load(fis);
			}
		} catch (FileNotFoundException e) {
			System.out.println("error file not found in convert");
			e.printStackTrace();
			return null;
		} catch (IOException e2) {
			System.out.println("error IO not found in convert");
			e2.printStackTrace();
			return null;
		} finally {
			try {
				if (fis != null) {
					fis.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		TransactionTraffic newLog = new TransactionTraffic();

		if (prop.getProperty("MSG_ID") != null)
			newLog.setTcdMsgId(prop.getProperty("MSG_ID"));

		if (prop.getProperty("REF_MSG_ID") != null)
			newLog.setTcdRefMsgId(prop.getProperty("REF_MSG_ID"));

		if (prop.getProperty("FROM_PARTY_ID") != null)
			newLog.setTcdPartyId(prop.getProperty("FROM_PARTY_ID"));

		if (prop.getProperty("FROM_PARTY_TYPE") != null)
			newLog.setTcdPartyType(prop.getProperty("FROM_PARTY_TYPE"));

		if (prop.getProperty("TO_PARTY_ID") != null)
			newLog.setJpmcPartyId(prop.getProperty("TO_PARTY_ID"));

		if (prop.getProperty("TO_PARTY_TYPE") != null)
			newLog.setJpmcPartyType(prop.getProperty("TO_PARTY_TYPE"));

		if (prop.getProperty("PAYLOAD_NAME") != null)
			newLog.setTcdAttach(prop.getProperty("PAYLOAD_NAME"));
		
		if (prop.getProperty("ACTION") != null)
			newLog.setActionType(prop.getProperty("ACTION"));

		newLog.setTcdCtrl(ctrlFile.getName());

		return newLog;
	}
	
	public static TransactionTraffic convertResultsetToLog(ResultSet rs) {
		TransactionTraffic traff = null;

		try {
			if (rs.next()) {
				traff = new TransactionTraffic();
				traff.setTransactionId((rs.getInt("transactionid")));
				traff.setAction(rs.getString("action"));
				traff.setProcess(rs.getString("process"));
				traff.setEventTime(Double.toString(rs.getDouble("time")));
				traff.setTcdMsgId(rs.getString("tcdmsgid"));
				traff.setTcdRefMsgId(rs.getString("tcdrefmsgid"));
				traff.setJpmcMsgId(rs.getString("jpmcmsgid"));
				traff.setTcdPartyId(rs.getString("tcdpartyid"));
				traff.setTcdPartyType(rs.getString("tcdpartytype"));
				traff.setJpmcPartyId(rs.getString("jpmcpartyid"));
				traff.setJpmcPartyType(rs.getString("jpmcpartytype"));
				traff.setTcdAttach(rs.getString("tcdattach"));
				traff.setTcdCtrl(rs.getString("tcdctrl"));
				traff.setJpmcAttach(rs.getString("jpmcattach"));
				traff.setJpmcCtrl(rs.getString("jpmcctrl"));
				traff.setConvertFile((rs.getString("convertfile")));
				traff.setType(rs.getString("type"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return traff;

	}
	
	public static PreparedStatement convertFindTraffic(Integer transactionId,String page,
			String limit, String sidx, String sord,Connection con) throws SQLException {
		Integer startList = Integer.parseInt(limit) * (Integer.parseInt(page) - 1);
        Integer endList = Integer.parseInt(limit) * Integer.parseInt(page);
        
        PreparedStatement st = null;
        
		String strOrderBy = " ORDER BY traffic." + sidx + " " + sord;
		String strLimit = " LIMIT "+startList.toString()+", "+endList.toString();
		
		String strSql = "select * from traffic where transactionid like ? ";
        strSql += strOrderBy;
        strSql += strLimit;
		
		
		/*PreparedStatement st = con
				.prepareStatement("select * from traffic left join transaction on traffic.transactionid=transaction.id where transaction.conversationid like ?order by traffic.time desc");
		*/
        st = con.prepareStatement(strSql);
		st.setInt(1, transactionId);
		return st;

	}

	public static PreparedStatement convertFindBilling(String fromTime,String toTime,String organize,String page,
			String limit, String sidx, String sord,Connection con) throws SQLException {
		Integer startList = Integer.parseInt(limit) * (Integer.parseInt(page) - 1);
        Integer endList = Integer.parseInt(limit) * Integer.parseInt(page);
        
        PreparedStatement st = null;
        
		
		String strLimit = " LIMIT "+startList.toString()+", "+endList.toString();
		
		String strSql = " SELECT  timestamp(time) as tmp,count(*) as sum from traffic where ";
        
		
		String strOrganize = "traffic.type='"+organize+"' and ";
    	String strFromTime = ((fromTime.equals("000000.000")||fromTime==null)?"true and ":"time >= "+fromTime+ " and ");
		String strToTime = 	((toTime.equals("999999.999")||toTime==null)?"true and ":"time <= "+toTime+ " and ");	
		String strProcess = " PROCESS =  ? ";
        String strGroupBy = "GROUP BY SUBSTR( TIME, 1, 8 )";
        
        strSql += strOrganize;
        strSql += strFromTime;
        strSql += strToTime;
        strSql += strProcess;
        strSql += strGroupBy;

        strSql += strLimit;
        
        st = con.prepareStatement(strSql);

        if(organize.equals("TCD")){
        	st.setString(1, TransactionManager.finSendTcdStatus);
        }else{
        	st.setString(1, TransactionManager.finSendRdStatus);
        }
		
		return st;

	}
	
	public static PreparedStatement convertCountBilling(String fromTime,String toTime,String organize,Connection con) throws SQLException {

        
        PreparedStatement st = null;
        
		String strSql = "SELECT COUNT( * ) AS sum FROM traffic WHERE ";
		String strOrganize = "type='"+organize+"' and ";
		String strFromTime = ((fromTime.equals("000000.000")||fromTime==null)?"true and ":"time >= "+fromTime+ " and ");
		String strToTime = 	((toTime.equals("999999.999")||toTime==null)?"true and ":"time <= "+toTime+ " and ");	
		String strProcess = " PROCESS =  ?";
		
		strSql += strOrganize;
		strSql += strFromTime;
        strSql += strToTime;
        strSql += strProcess;

        st = con.prepareStatement(strSql);
        if(organize.equals("TCD")){
        	st.setString(1, TransactionManager.finSendTcdStatus);
        }else{
        	st.setString(1, TransactionManager.finSendRdStatus);
        }
		
		return st;

	}
	
	public static PreparedStatement convertFindResendTCD(String xmlFile,
			String type ,String page,
			String limit, String sidx, String sord,Connection con) throws SQLException {
        
        PreparedStatement st = null;
        String strSql = "";
		if(type.equals("jpmc") || type.equals("jpmcrd")){
			  strSql = "select count(*) as sum from traffic where tcdattach = ? and (process = ? or process = ?) ";
			  st = con.prepareStatement(strSql);
			  st.setString(1, xmlFile);
			  st.setString(2,TransactionManager.resendFinSFTPStatus);
			  st.setString(3,TransactionManager.resendFinSFTPStatusRD);
			 
		}else{
			 strSql = "select count(*) as sum from traffic where jpmcattach = ? and (process = ? or process = ?)";
			  st = con.prepareStatement(strSql);
				st.setString(1, xmlFile);
				st.setString(2,TransactionManager.redownloadFinSFTPStatus);
				st.setString(3,TransactionManager.redownloadFinSFTPStatusRD);
		}
		
		return st;

	}
	
	
	public static List convertResultsetTrafficToDisplay(ResultSet rs)
	throws SQLException {
		List displayList = new ArrayList();
		JQGridRow row;
		SimpleDateFormat sdf = new
		SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
		while (rs.next()) {
			
			row = new JQGridRow();
			List display = new ArrayList();
			display.add((String) rs.getObject("action"));
			display.add((String) rs.getObject("process"));
			if(((String) rs.getObject("tcdattach"))!= null){
				display.add((String) rs.getObject("tcdmsgid"));
				
			}else{
				display.add((String) rs.getObject("jpmcmsgid"));
				
			}
			display.add((String) rs.getObject("convertfile"));
			String timeStr = rs.getString("time");
			display.add(TransactionManager.convertStrToTmsStr(timeStr));
			row.setCell(display);
			displayList.add(row);
		}
	
	return displayList;
}
	
	public static List convertResultsetBillingToDisplay(ResultSet rs)
	throws SQLException {
		List displayList = new ArrayList();
		JQGridRow row;
		SimpleDateFormat sdf = new
		SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
		while (rs.next()) {
			
			row = new JQGridRow();
			List display = new ArrayList();
			Timestamp timestamp = (Timestamp) rs.getObject("tmp");
			String time = convertDateToString(timestamp);
			display.add(time);
			display.add((String) rs.getObject("sum").toString());
		
			row.setCell(display);
			displayList.add(row);
		}
	
	return displayList;
}
	
	
	
	
	
	public static PreparedStatement convertCountTraffic(Integer transactionId,Connection con) throws SQLException {
		

		PreparedStatement st = con
				.prepareStatement("SELECT COUNT( * ) AS sum FROM traffic WHERE transactionid =? ");
		
		st.setInt(1, transactionId);
		return st;

	}
	

	
public void insertJpmcDetail(String attachName,TransactionLog log) {	
		//String sendFileName = Worker.convertJPMCToTCD(attachName);
		setJpmcAttach(attachName);
		setJpmcCtrl(genJpmcCtrlName());
		setConvertFile(attachName);
		Date now = new Date();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss");

		String msgId = formatter.format(now);
		String Id = String.format("%05d", log.getId());

		msgId += Id + "@" + SFTPManager.getJPMCIp();

		setJpmcMsgId(msgId);

	}

public String genJpmcCtrlName() {
	String attach = getJpmcAttach();
	String ctrl = attach.substring(0, attach.length() - 3) + "ctrl";
	return ctrl;
}
	

	public static PreparedStatement convertFindByTransactionId(Integer transactionId,
			Connection con) throws SQLException {
		PreparedStatement st = con
				.prepareStatement("SELECT * FROM traffic WHERE transactionid = ? AND (process = ? or process = ?)AND TIME = " +
						"(SELECT MAX( TIME )FROM traffic WHERE transactionid =? and (process = ? or process = ?)) ");
		st.setInt(1, transactionId);
		st.setString(2, TransactionManager.finSendJpmcStatus);
		st.setString(3, TransactionManager.finSendJpmcStatusRD);
		st.setInt(4, transactionId);
		st.setString(5, TransactionManager.finSendJpmcStatus);
		st.setString(6, TransactionManager.finSendJpmcStatusRD);
		
		
		return st;

	}
	
	public static PreparedStatement convertFindByTransactionIdForResend(Integer transactionId,
			Connection con) throws SQLException {
		PreparedStatement st = con
				.prepareStatement("SELECT * FROM traffic WHERE transactionid = ? AND TIME = " +
						"(SELECT MAX( TIME )FROM traffic WHERE transactionid =? ) ");
		st.setInt(1, transactionId);
		st.setInt(2, transactionId);
		
		
		return st;

	}
	
	public static PreparedStatement convertFindTraffSendTCDByTransactionId(Integer transactionId,
			Connection con) throws SQLException {
		PreparedStatement st = con
				.prepareStatement("SELECT * FROM traffic WHERE transactionid = ? AND process = ? AND TIME = " +
						"(SELECT MAX( TIME )FROM traffic WHERE transactionid =? and process = ?) ");
		st.setInt(1, transactionId);
		st.setString(2, TransactionManager.sendTcdStatus);
		st.setInt(3, transactionId);
		st.setString(4, TransactionManager.sendTcdStatus);
		
		
		return st;

	}
	
	public static PreparedStatement convertFindTraffSendRDByTransactionId(Integer transactionId,
			Connection con) throws SQLException {
		PreparedStatement st = con
				.prepareStatement("SELECT * FROM traffic WHERE transactionid = ? AND process = ? AND TIME = " +
						"(SELECT MAX( TIME )FROM traffic WHERE transactionid =? and process = ?) ");
		st.setInt(1, transactionId);
		st.setString(2, TransactionManager.sendRdStatus);
		st.setInt(3, transactionId);
		st.setString(4, TransactionManager.sendRdStatus);
		
		
		return st;

	}

	
	
	public static PreparedStatement convertInsert(TransactionTraffic traff,Connection con) {
		
		PreparedStatement st = null;
		try {
		st = con.prepareStatement("insert into traffic(id,transactionid,action,process,time," +
				"tcdmsgid,tcdrefmsgid,jpmcmsgid," +
				"tcdpartyid,tcdpartytype,jpmcpartyid,jpmcpartytype," +
				"tcdattach,tcdctrl,jpmcattach,jpmcctrl,convertfile,type)values(NULL,?,?,?,?," +
				"?,?,?,?," +
				"?,?,?,?," +
				"?,?,?,?,?)");
		
		st.setInt(1, traff.getTransactionId());
		st.setString(2, traff.getAction());
		st.setString(3, traff.getProcess());
		st.setString(4, traff.getEventTime());
		st.setString(5, traff.getTcdMsgId());
		st.setString(6, traff.getTcdRefMsgId());
		st.setString(7, traff.getJpmcMsgId());
		st.setString(8, traff.getTcdPartyId());
		st.setString(9, traff.getTcdPartyType());
		st.setString(10, traff.getJpmcPartyId());
		st.setString(11, traff.getJpmcPartyType());
		st.setString(12, traff.getTcdAttach());
		st.setString(13, traff.getTcdCtrl());
		st.setString(14, traff.getJpmcAttach());
		st.setString(15, traff.getJpmcCtrl());
		st.setString(16, traff.getConvertFile());
		st.setString(17, traff.getType());
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		return st;
	}
	
	public PreparedStatement convertQueryTraffic(Connection con,TransactionLog log){
		
		PreparedStatement st = null;
		try {
		st = con.prepareStatement("select * from traffic where transactionid = ?");
		
		st.setInt(1, log.getId());
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		return st;
	}

	public static String convertDateToString(Timestamp now2){
		 String curTime =""; 
		 String date  = Integer.toString(now2.getDate());
		 String month = Integer.toString(now2.getMonth()+1);
		 String year  = Integer.toString(now2.getYear()+1900);

		 if(date.length()==1)date = "0"+date;
		 if(month.length()==1)month = "0"+month;
			
	
			curTime +=year;
			curTime +="-";
			curTime +=month;
			curTime +="-";
			curTime +=date;
			
			return curTime;
	}
}
